﻿using CommonLib;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using YouErYuanModels;

namespace YouErYuanBLL
{
    public class RecipeBLL
    {
        /// <summary>
        /// 查询食谱
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public object SelectForId(string id)
        {
            string sql = "select * from T_Recipe where id=@id";
            DataTable dt = SQLHepler.GetDataTable(sql, new SqlParameter("@id", id));
            return dt;
        }

        /// <summary>
        /// 查询食谱
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        //public object SelectForDate(string date)
        //{
        //    string sql = string.Format("select * from T_Recipe where startDate='{0}'", date);
        //    DataTable dt = SQLHepler.GetDataTable(sql, null);
        //    return dt;
        //}

        /// <summary>
        /// 分页查询幼儿园食谱记录
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="youErYuanName">幼儿园名称</param>
        /// <param name="total">条件查询总记录数</param>
        /// <param name="times">条件查询时间</param>
        /// <returns></returns>
        public object RispePageList(int pageIndex, int pageSize, string schoolId, string times, out int total)
        {
            string where = "";
            if (string.IsNullOrEmpty(schoolId) == false)
            {
                schoolId = SqlFilter.Filter(schoolId);
                where = " schoolid = '" + schoolId + "'";
            }
            if (!string.IsNullOrEmpty(times))
            {
                times = SqlFilter.Filter(times);
                where += " and startDate='" + times + "' ";
            }
            //根据时间查询
            DataTable dt = SQLHepler.GetPaging("a.[startDate] desc", "[T_Recipe] a left join t_dictionary b on a.type=b.id ", where, pageIndex, pageSize, out total);
            return dt;
        }
        /// <summary>
        /// 查询类型
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public DataSet getTypes()
        {
            string sql = @"select * From t_dictionary where pid=20000;
                           select * From t_dictionary where pid=30000;";
            return SQLHepler.GetDataSet(sql);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="ris"></param>
        /// <returns></returns>
        public int DelData(TRecipe ris)
        {
            string sql = "delete T_Recipe where id=@id";
            SqlParameter[] par = new SqlParameter[] {
                new SqlParameter("@id",ris.id)
            };
            return SQLHepler.ExecuteSql(sql, par);
        }

        /// <summary>
        /// 更新食谱数据
        /// </summary>
        /// <returns></returns>
        public object InsertOrUpdate(string type, TRecipe Rispe)
        {
            string sql = "";
            //验证是否重复语句
            string sqls = "select top 2 id,type,startDate  from T_Recipe where startdate=@startDate and type=@type";
            //新增
            string sqlAdd = "insert into T_Recipe (id,footName,type,startDate,schoolId,footImg) values (newid(),@footName,@type,@startDate,@schoolId,@footImg)";
            //修改
            string sqlUpt = "update T_Recipe set footName=@footName,type=@type,startDate=@startDate,footImg=@footImg where id=@id";
            sql = type == "add" ? sqlAdd : sqlUpt;
            SqlParameter[] parameter = new SqlParameter[] {
                new SqlParameter("@type",Rispe.type+""),
                new SqlParameter("@startDate",Rispe.startDate+""),
                new SqlParameter("@Id",Rispe.id),
                new SqlParameter("@footName",Rispe.footName+""),
                new SqlParameter("@footImg",Rispe.footImg+""),
                new SqlParameter("@schoolId",Rispe.schoolId)
            };
            DataTable dt = SQLHepler.GetDataTable(sqls, parameter);
            if (dt != null && dt.Rows.Count > 0)
            {
                bool isTypes = dt.Rows[0]["type"] + "" == Rispe.type && Convert.ToDateTime(dt.Rows[0]["startDate"]) == Rispe.startDate;
                bool isID = dt.Rows[0]["id"] + "" == Rispe.id;
                //验证需要修改的记录是否已存在
                if (type == "add" || !isID && isTypes)
                {
                    return 3;
                }
            }

            int count = SQLHepler.ExecuteSql(sql, parameter);
            return count;
        }
    }
}
